import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
import matplotlib.ticker as tk
plt.rcParams['font.sans-serif'] = ['SimHei']
shu_ju=pd.read_excel('./学生课程成绩数据.xls')
name=['单莹','陈卓','崔泽远','曹嘉明']
course=['生物化学','土壤学','体育（2）','专业劳动']
data_1=shu_ju[['姓名','课程名称','分数']]
# data_s=[[] for  i in range(4)]
data_array=np.array(data_1)
# for i in range(len(data_array)):
#     for j in range(4):
#         for k in range(4):
#             if(data_array[i][0]==name[j] and data_array[i][1]==course[k]):
#                 data_s[j].append(data_array[i][2])
data_s=[[0 for i in range(4)] for i in range(4)]
for i in range(len(data_array)):
    for j in range(4):
        for k in range(4):
            if(data_array[i][0]==name[j] and data_array[i][1]==course[k]):
                data_s[j][k]=data_array[i][2]
data_T=np.transpose(data_s)#转置
width=0.2
x=np.array([2,3.5,5,6.5])
fig=plt.figure(figsize=(5,4))
ax=fig.add_axes([0.1,0.5,0.8,0.4])#[左下角x，左下角y，宽度，高度]
ax.bar(x-width/2*3,data_T[0],label='生物化学',width=width)
ax.bar(x-width/2,data_T[1],label='土壤学',width=width)
ax.bar(x+width/2,data_T[2],label='体育（2）',width=width)
ax.bar(x+width/2*3,data_T[3],label='专业劳动',width=width)
ax.legend(loc=0,fontsize=4,markerscale=0.1)
ax.set_xticks(x)
ax.set_xticklabels(name)
#插入数据表
my_table=ax.table(cellText=data_s,loc='bottom',cellColours=[['gray' for c in range(4)] for r in range(4)],
                  bbox=[0.25,-0.7,0.5,0.5],rowLabels=name,colLabels=course)
plt.show()
# print(data_s)
